﻿Imports microsoft.Data.SQLite
Public Class 待办清单
    Private Sub Label1_Click(sender As Object, e As EventArgs) Handles Label1.Click

        Dim tw As Double

        With Label1
            If .Text = ">" Then
                .Left = 0
                .Top = 0
                .Width = 17
                .Height = 17
                .Text = "<"
                主窗体.TableLayoutPanel1.ColumnStyles(2) = New ColumnStyle(SizeType.Absolute, 40)
                tw = 主窗体.TableLayoutPanel1.ColumnStyles(0).Width + 主窗体.TableLayoutPanel1.ColumnStyles(2).Width
                主窗体.TableLayoutPanel1.ColumnStyles(1) = New ColumnStyle(SizeType.Absolute, 主窗体.Width - tw)
                DataGridView1.Hide()
                GroupBox1.Hide()
                TextBox1.Hide()
                MonthCalendar1.Hide()
            Else
                主窗体.TableLayoutPanel1.ColumnStyles(2) = New ColumnStyle(SizeType.Absolute, 550)
                tw = 主窗体.TableLayoutPanel1.ColumnStyles(0).Width + 主窗体.TableLayoutPanel1.ColumnStyles(2).Width
                主窗体.TableLayoutPanel1.ColumnStyles(1) = New ColumnStyle(SizeType.Absolute, 主窗体.Width - tw)
                .Left = 0
                .Top = 0
                .Width = 17
                .Height = 17
                .Text = ">"
                DataGridView1.Show()
                GroupBox1.Show()
                TextBox1.Show()
                MonthCalendar1.Show()
            End If
        End With

    End Sub

    ' 列名称常量（替代硬编码索引）
    Private Const ID_COL As String = "ID"
    Private Const TASK_COL As String = "待办事项"
    Private Const STATUS_COL As String = "状态"
    Private Const UPDATE_COL As String = "更新时间"

    ' 单元格点击事件 
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        If e.RowIndex < 0 OrElse e.ColumnIndex <> 0 Then Exit Sub
        Try
            Dim currentRow = DataGridView1.Rows(e.RowIndex)
            If currentRow.Cells(STATUS_COL).Value = "待完成" Then
                currentRow.Cells(STATUS_COL).Value = "已完成"
                Dim newStatus As String = currentRow.Cells(STATUS_COL).Value
                currentRow.Cells(STATUS_COL).Value = newStatus
                currentRow.Cells(UPDATE_COL).Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm")

                UpdateDatabase(currentRow.Cells(ID_COL).Value.ToString(), newStatus, currentRow.Cells(TASK_COL).Value)
            ElseIf currentRow.Cells(STATUS_COL).Value = "已完成" Then
                currentRow.Cells(STATUS_COL).Value = "待完成"
                Dim newStatus As String = currentRow.Cells(STATUS_COL).Value
                currentRow.Cells(STATUS_COL).Value = newStatus
                currentRow.Cells(UPDATE_COL).Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm")

                UpdateDatabase(currentRow.Cells(ID_COL).Value.ToString(), newStatus, currentRow.Cells(TASK_COL).Value)
            End If
        Catch ex As Exception
            MessageBox.Show($"操作异常：{ex.Message}")
        End Try
    End Sub

    Private Sub DataGridView1_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DataGridView1.CellBeginEdit

        If e.ColumnIndex = 0 Then
            e.Cancel = True ' 取消编辑,阻止双击干扰
        Else
            e.Cancel = False ' 恢复编辑
        End If

    End Sub

    ' 数据库更新方法（带事务）
    Private Sub UpdateDatabase(taskId As String, newStatus As String, todolist As String)
        ' 确保连接字符串有效 
        If String.IsNullOrEmpty(_connString) Then
            MessageBox.Show("数据库连接字符串为空，请检查配置。")
            Return
        End If

        Using connection As New SqliteConnection(_connString)
            Try
                ' 打开数据库连接 
                connection.Open()

                ' 检查事项是否已存在 
                Dim checkSql As String = "SELECT 待办事项 FROM todolist WHERE 序号 = @id"
                Using checkCmd As New SqliteCommand(checkSql, connection)
                    checkCmd.Parameters.AddWithValue("@id", taskId)
                    Using reader As SqliteDataReader = checkCmd.ExecuteReader()
                        If reader.HasRows Then
                            reader.Close()
                            ' 事项已存在，执行更新操作 
                            Dim cmdText = "UPDATE todolist SET 序号=@id, 状态=@status, 更新时间=@updateTime, 待办事项=@todolist WHERE 序号=@id"
                            ExecuteCommand(connection, cmdText, taskId, newStatus, todolist)
                        End If
                    End Using
                End Using
            Catch ex As Exception
                ' 记录异常信息到日志 
                System.Diagnostics.Debug.WriteLine($"操作失败: {ex.Message}")
                MessageBox.Show($"操作失败: {ex.Message}")
            End Try
        End Using

    End Sub

    Private Sub ExecuteCommand(connection As SqliteConnection, cmdText As String, taskId As String, newStatus As String, todolist As String)
        Using cmd As New SqliteCommand(cmdText, connection)
            cmd.Parameters.AddWithValue("@status", newStatus)
            cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
            cmd.Parameters.AddWithValue("@todolist", todolist)
            cmd.Parameters.AddWithValue("@id", taskId)
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Private Sub ExecuteCommand1(connection As SqliteConnection, cmdText As String, Optional taskId As String = "", Optional newStatus As String = "待完成", Optional todolist As String = "")
        Using cmd As New SqliteCommand(cmdText, connection)
            If Not String.IsNullOrEmpty(newStatus) Then cmd.Parameters.AddWithValue("@status", newStatus)
            If Not String.IsNullOrEmpty(todolist) Then cmd.Parameters.AddWithValue("@todolist", todolist)
            If Not String.IsNullOrEmpty(taskId) Then cmd.Parameters.AddWithValue("@id", taskId)
            cmd.Parameters.AddWithValue("@updateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm"))
            cmd.ExecuteNonQuery()
        End Using
    End Sub
    Private Sub 待办清单_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' 获取用户选择的公历日期
        Dim selectDate As DateTime = Now
        With Lunar.Lunar.FromDate(selectDate)
            TextBox1.Text = "【农历】 " & .YearInGanZhi & .YearShengXiao & "年" & vbCrLf &
                            "【标准纪年】" & .YearInChinese & "年" & vbCrLf &
                            "【当前月日】" & .MonthInChinese & "月" & .DayInChinese & vbCrLf &
                            "【干支月日】" & .MonthInGanZhi & "月 " & .DayInGanZhi & "日" & vbCrLf &
                            "【地支时辰】" & String.Join(" ", .TimeZhi) & vbCrLf &
                            "════════════════" & vbCrLf &
                            "【宜】" & String.Join(" ", .DayYi) & vbCrLf &
                            "【忌】" & String.Join(" ", .DayJi)
        End With
        QueryAndShowData()
        ConfigureGridViewColumns()

    End Sub
    Public Sub QueryAndShowData()

        Using connection As New SqliteConnection(_connString)
            Try
                connection.Open()
                Dim strsql As String = "SELECT 序号 as ID,待办事项,状态,更新时间 FROM todolist ORDER BY 序号"
                Using cmd As New SqliteCommand(strsql, connection)
                    Using reader As SqliteDataReader = cmd.ExecuteReader()
                        Dim dataTable As New DataTable()
                        dataTable.Load(reader)
                        If dataTable.Rows.Count > 0 Then
                            DataGridView1.DataSource = dataTable
                        End If
                    End Using
                End Using
            Catch ex As Exception
                Debug.WriteLine($"异常详情：{ex.ToString()}")
                MessageBox.Show($"操作失败：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

        End Using

    End Sub

    Private Sub ConfigureGridViewColumns()
        With DataGridView1
            .Columns("选择").Width = 40
            .Columns("ID").Width = 40
            .Columns("待办事项").Width = 250
            .Columns("状态").Width = 50
            .Columns("更新时间").Width = 115
            .Columns("ID").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells  ' 包含标题行

            Label3.Top = (GroupBox1.Height - Label3.Height) / 2
            Label3.Left = (GroupBox1.Width - Label3.Width) / 4
        End With
    End Sub

    Private Sub DataGridView1_DataBindingComplete(sender As Object, e As DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
        For Each row As DataGridViewRow In DataGridView1.Rows
            If Not row.IsNewRow Then
                SyncCheckbox(row)
            End If
        Next
    End Sub

    Private Sub SyncCheckbox(row As DataGridViewRow)
        Dim status = row.Cells("状态").Value?.ToString()
        Dim checkCell = CType(row.Cells("选择"), DataGridViewCheckBoxCell)

        If status = "已完成" Then
            row.DefaultCellStyle.BackColor = Color.DarkBlue
            row.DefaultCellStyle.ForeColor = Color.White
            checkCell.Value = CheckState.Checked
        Else
            row.DefaultCellStyle.BackColor = Color.White
            row.DefaultCellStyle.ForeColor = Color.Black
            checkCell.Value = CheckState.Unchecked
        End If
    End Sub
    Private Sub DataGridView1_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
        If e.RowIndex < 0 OrElse e.ColumnIndex < 0 Then Exit Sub
        Dim row = DataGridView1.Rows(e.RowIndex)
        SyncCheckbox(row)
    End Sub

    Private Sub CheckBox1_CheckedChanged(sender As Object, e As EventArgs) Handles CheckBox1.CheckedChanged
        Using connection As New SqliteConnection(_connString)
            ' 打开数据库连接 
            connection.Open()
            Dim cmdText As String = "UPDATE todolist SET 状态 = @newStatus"
            Using cmd As New SqliteCommand(cmdText, connection)
                If CheckBox1.Checked Then
                    cmd.Parameters.AddWithValue("@newStatus", "已完成")
                Else
                    cmd.Parameters.AddWithValue("@newStatus", "待完成")
                End If
                cmd.ExecuteNonQuery()
            End Using
        End Using
        QueryAndShowData()
    End Sub

    Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
        Label3.Text = Now
    End Sub

    Private Sub MonthCalendar1_DateChanged(sender As Object, e As DateRangeEventArgs) Handles MonthCalendar1.DateChanged
        ' 获取用户选择的公历日期
        Dim selectDate As DateTime = MonthCalendar1.SelectionStart
        With Lunar.Lunar.FromDate(selectDate)
            TextBox1.Text = "【农历】 " & .YearInGanZhi & .YearShengXiao & "年" & vbCrLf &
                            "【标准纪年】" & .YearInChinese & "年" & vbCrLf &
                            "【当前月日】" & .MonthInChinese & "月" & .DayInChinese & vbCrLf &
                            "【干支月日】" & .MonthInGanZhi & "月 " & .DayInGanZhi & "日" & vbCrLf &
                            "【地支时辰】" & String.Join(" ", Lunar.Lunar.FromDate(Now).TimeZhi) & vbCrLf &
                            "════════════════" & vbCrLf &
                            "【宜】" & String.Join(" ", .DayYi) & vbCrLf &
                            "【忌】" & String.Join(" ", .DayJi)
        End With
    End Sub
    'Datagridview1右击事件：按下右键时，显示“上移”和“下移”按钮，上移与上一行交换数据，下移与下一行交换数据
    Private Sub DataGridView1_CellMouseDown(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDown
        If e.Button = MouseButtons.Right AndAlso e.RowIndex >= 0 Then
            ' 选中当前右键点击的行 
            DataGridView1.ClearSelection()
            DataGridView1.Rows(e.RowIndex).Selected = True
            DataGridView1.CurrentCell = DataGridView1.Rows(e.RowIndex).Cells(0)
        End If
    End Sub
    Private Sub 上移ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 上移ToolStripMenuItem.Click
        ' 检查当前行是否为空 
        If DataGridView1.CurrentRow Is Nothing Then
            Return
        End If

        ' 检查当前行是否为第一行 
        If DataGridView1.CurrentRow.Index = 0 Then
            Return
        End If

        Dim currentRow = DataGridView1.CurrentRow
        Dim currentRowId = currentRow.Cells(ID_COL).Value.ToString()
        Dim prevRow = DataGridView1.Rows(currentRow.Index - 1)
        Dim prevRowId = prevRow.Cells(ID_COL).Value.ToString()

        ' 获取当前行和前一行的待办事项内容 
        Dim currentTask = currentRow.Cells(TASK_COL).Value.ToString()
        Dim prevTask = prevRow.Cells(TASK_COL).Value.ToString()

        Using connection As New SqliteConnection(_connString)
            connection.Open()
            ' 更新前一行内容为当前行内容 
            ExecuteCommand1(connection, "UPDATE todolist SET 待办事项=@todolist WHERE 序号=@id", taskId:=prevRowId, todolist:=currentTask)
            ' 更新当前行内容为前一行内容 
            ExecuteCommand1(connection, "UPDATE todolist SET 待办事项=@todolist WHERE 序号=@id", taskId:=currentRowId, todolist:=prevTask)
        End Using

        QueryAndShowData()
    End Sub

    Private Sub 下移ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 下移ToolStripMenuItem.Click
        ' 检查当前行是否为空 
        If DataGridView1.CurrentRow Is Nothing Then
            Return
        End If

        ' 检查当前行是否为第一行 
        If DataGridView1.CurrentRow.Index = 0 Then
            Return
        End If

        Dim currentRow = DataGridView1.CurrentRow
        Dim currentRowId = currentRow.Cells(ID_COL).Value.ToString()
        Dim nextRow = DataGridView1.Rows(currentRow.Index + 1)
        Dim nextRowId = nextRow.Cells(ID_COL).Value.ToString()

        ' 获取当前行和前一行的待办事项内容 
        Dim currentTask = currentRow.Cells(TASK_COL).Value.ToString()
        Dim prevTask = nextRow.Cells(TASK_COL).Value.ToString()

        Using connection As New SqliteConnection(_connString)
            connection.Open()
            ' 更新前一行内容为当前行内容 
            ExecuteCommand1(connection, "UPDATE todolist SET 待办事项=@todolist WHERE 序号=@id", taskId:=nextRowId, todolist:=currentTask)
            ' 更新当前行内容为前一行内容 
            ExecuteCommand1(connection, "UPDATE todolist SET 待办事项=@todolist WHERE 序号=@id", taskId:=currentRowId, todolist:=prevTask)
        End Using

        QueryAndShowData()
    End Sub

    Private Sub ReverseDatabase(taskId As String, todolist As String)
        ' 确保连接字符串有效 
        If String.IsNullOrEmpty(_connString) Then
            MessageBox.Show("数据库连接字符串为空，请检查配置。")
            Return
        End If

        Using connection As New SqliteConnection(_connString)
            Try
                ' 打开数据库连接 
                connection.Open()
                ' 检查事项是否已存在 
                Dim checkSql As String = "SELECT 待办事项 FROM todolist WHERE 序号 = @id"
                Using checkCmd As New SqliteCommand(checkSql, connection)
                    checkCmd.Parameters.AddWithValue("@id", taskId)
                    Using reader As SqliteDataReader = checkCmd.ExecuteReader()
                        If reader.HasRows Then
                            reader.Close()
                            ' 事项已存在，执行更新操作 
                            Dim cmdText = "UPDATE todolist SET 待办事项=@todolist WHERE 序号=@id"
                            ExecuteCommand1(connection, cmdText, taskId, todolist)

                        End If
                    End Using
                End Using
            Catch ex As Exception
                ' 记录异常信息到日志 
                System.Diagnostics.Debug.WriteLine($"操作失败: {ex.Message}")
                MessageBox.Show($"操作失败: {ex.Message}")
            End Try
        End Using
        QueryAndShowData()
    End Sub

    Private Sub 新增行ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 新增行ToolStripMenuItem.Click

        ' 检查当前行是否为空 
        If DataGridView1.CurrentRow Is Nothing Then
            Return
        End If

        ' 检查当前行是否为第一行 
        If DataGridView1.CurrentRow.Index = 0 Then
            Return
        End If

        Dim currentRow = DataGridView1.CurrentRow
        Dim currentRowId = currentRow.Cells(ID_COL).Value.ToString()
        Dim currentTask = currentRow.Cells(TASK_COL).Value.ToString()

        Using connection As New SqliteConnection(_connString)
            connection.Open()
            ' 新增行 
            ExecuteCommand1(connection, "INSERT INTO todolist (状态, 更新时间, 待办事项) VALUES(@status, @updateTime, @todolist)", taskId:=currentRowId, todolist:=currentTask)

        End Using

        QueryAndShowData()

    End Sub

    Private Sub 删除行ToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles 删除行ToolStripMenuItem.Click

        ' 检查当前行是否为空 
        If DataGridView1.CurrentRow Is Nothing Then
            Return
        End If

        ' 检查当前行是否为第一行 
        If DataGridView1.CurrentRow.Index = 0 Then
            Return
        End If

        Dim currentRow = DataGridView1.CurrentRow
        Dim currentRowId = currentRow.Cells(ID_COL).Value.ToString()

        Using connection As New SqliteConnection(_connString)
            connection.Open()
            ' 新增行 
            ExecuteCommand1(connection, "delete from todolist WHERE 序号 = @id", taskId:=currentRowId)

        End Using

        QueryAndShowData()
    End Sub
End Class